package com.navi.loaddata.loadService.impl.arrayCfMappingData;

import com.navi.loaddata.core.errorLog.ErrorCode;
import com.navi.loaddata.core.etlException.ETLException;
import com.navi.loaddata.core.etlException.ETLException2;
import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.oracleEntity.fileMapping.CellArrayCfMappingTModel;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.util.JdbcUtil;
import com.navi.loaddata.core.util.LoggerUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

@Service
public class LoadArrayCfMappingInfoService {
    private static final Logger logger = LoggerFactory.getLogger(LoadArrayCfMappingInfoService.class);
    private LoggerUtil loggerUtil = new LoggerUtil(this.getClass());


    public List<CellArrayCfMappingTModel> loadArrayCfInfo(EtlConfD etlConfD) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String startTime = sdf.format(etlConfD.getRunStartTimestamp().getTime());
        String endTime = sdf.format(etlConfD.getRunEndTimestamp().getTime());

        List<CellArrayCfMappingTModel> cshtList = new ArrayList<>();

        try {
            QueryRunner qr_gp = JdbcUtil.getQueryRunner_gp();
//        String sql = " select" +
//                " CASE WHEN b.tft_glass_id IS NOT NULL THEN b.tft_glass_id ELSE '-1'||a.glass_id END AS arrayComponentId," +
//                " CASE WHEN b.cf_glass_id IS NOT NULL THEN b.cf_glass_id ELSE '-1' END AS cfComponentId," +
//                " a.logof_timestamp AS startTime," +
//                " '1' AS componentType," +
//                " CASE WHEN b.tft_glass_id  IS NOT NULL THEN b.tft_glass_id ELSE '-1' END AS arrayGlassId," +
//                " CASE WHEN b.cf_glass_id  IS NOT NULL THEN b.cf_glass_id ELSE '-1' END AS cfGlassId," +
//                " ary.lot_id AS arrayLotId," +
//                " cf.group_id AS cfLotId," +
//                " b.group_id AS cellBatchId," +
//                " now() AS updateTime," +
//                " b.group_id AS item1" +
//                " from wpp_csht_ope_his a" +
//                " left join (select panel_id,tft_glass_id,cf_glass_id,group_id from wpp_sht_info) as b on a.glass_id=b.panel_id" +
//                " left join (select panel_id,lot_id from wpp_sht_info) as ary on a.tft_glass_id=ary.panel_id" +
//                " left join (select panel_id,group_id from wpp_sht_info) as cf on a.cf_glass_id=cf.panel_id" +
//                " where 1=1" +
//                " and a.ope_id='L2100'" +
//                " and substring(glass_id,2,2)='18'" +
//                " and a.db_timestamp>='" + startTime + "'" +
//                " and a.db_timestamp<'" + endTime + "'";
            String cshtSql = " select" +
                    " a.glass_id as glassId," +
                    " a.logof_timestamp AS startTime," +
                    " '1' AS componentType," +
                    " now() AS updateTime" +
                    " from wpp_csht_ope_his as a" +
                    " where 1=1" +
                    " and a.ope_id='L2100'" +
                    " and substring(a.glass_id,2,2)='18'" +
                    " and length(a.glass_id)=10" +
                    " and a.db_timestamp>='" + startTime + "'" +
                    " and a.db_timestamp<'" + endTime + "'";

            cshtList = qr_gp.query(cshtSql, new BeanListHandler<CellArrayCfMappingTModel>(CellArrayCfMappingTModel.class));
            HashMap<String, CellArrayCfMappingTModel> cPanelMap = new HashMap<>();
            HashMap<String, CellArrayCfMappingTModel> acfPanelMap = new HashMap<>();
            if(!cshtList.isEmpty()) {//存在load数据
                String cshtGlassIds = getGlassIdsFromCsht(cshtList);
                String cPanelSql=" select" +
                        " b.panel_id as glassId," +
                        " CASE WHEN b.tft_glass_id IS NOT NULL THEN b.tft_glass_id ELSE '-1'||b.panel_id END AS arrayComponentId," +
                        " CASE WHEN b.cf_glass_id IS NOT NULL THEN b.cf_glass_id ELSE '-1' END AS cfComponentId," +
                        " CASE WHEN b.tft_glass_id  IS NOT NULL THEN b.tft_glass_id ELSE '-1' END AS arrayGlassId," +
                        " CASE WHEN b.cf_glass_id  IS NOT NULL THEN b.cf_glass_id ELSE '-1' END AS cfGlassId," +
                        " b.group_id AS cellBatchId," +
                        " b.group_id AS item1" +
                        " from wpp_sht_info b" +
                        " where 1=1" +
                        " and b.panel_id in ("+cshtGlassIds+")";
                List<CellArrayCfMappingTModel> cPanelList = qr_gp.query(cPanelSql, new BeanListHandler<CellArrayCfMappingTModel>(CellArrayCfMappingTModel.class));
                if(!cPanelList.isEmpty()) {//对应的sht_info里有数据
                    cPanelList.forEach(e->cPanelMap.put(e.getGlassId(),e));
                    String acfGlassIds=getGlassIdsFromCPanel(cPanelList);
                    String acfPanelSql=" select" +
                            " acf.panel_id as glassId," +
                            " acf.lot_id AS arrayLotId," +
                            " acf.group_id AS cfLotId" +
                            " from wpp_sht_info acf" +
                            " where 1=1" +
                            " and acf.panel_id in("+acfGlassIds+")";
                    List<CellArrayCfMappingTModel> acfPanelList = qr_gp.query(acfPanelSql, new BeanListHandler<CellArrayCfMappingTModel>(CellArrayCfMappingTModel.class));
                    if(!acfPanelList.isEmpty()){
                        acfPanelList.forEach(e-> acfPanelMap.put(e.getGlassId(),e));
                    }
                }

                for (CellArrayCfMappingTModel cell:cshtList) {
                    if(!cPanelMap.isEmpty()&&null!=cPanelMap.get(cell.getGlassId())) {
                        cell.setArrayComponentId(cPanelMap.get(cell.getGlassId()).getArrayComponentId());
                        cell.setCfComponentId(cPanelMap.get(cell.getGlassId()).getCfComponentId());
                        cell.setArrayGlassId(cPanelMap.get(cell.getGlassId()).getArrayGlassId());
                        cell.setCfGlassId(cPanelMap.get(cell.getGlassId()).getCfGlassId());
                        cell.setCellBatchId(cPanelMap.get(cell.getGlassId()).getCellBatchId());
                        cell.setItem1(cPanelMap.get(cell.getGlassId()).getItem1());
                        if(!acfPanelMap.isEmpty()&&null!=acfPanelMap.get(cell.getArrayGlassId())){
                            cell.setArrayLotId(acfPanelMap.get(cell.getArrayGlassId()).getArrayLotId());
                        }
                        if(!acfPanelMap.isEmpty()&&null!=acfPanelMap.get(cell.getCfGlassId())){
                            cell.setCfLotId(acfPanelMap.get(cell.getCfGlassId()).getCfLotId());
                        }
                    }else{
                        cell.setArrayComponentId("-1");
                        cell.setCfComponentId("-1");
                        cell.setArrayGlassId("-1");
                        cell.setCfGlassId("-1");

                    }
                }
            }
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_GP_ARY_CF_MAPPING, e);
        }
        return cshtList;
    }




    private String getGlassIdsFromCsht(List<CellArrayCfMappingTModel> cshtList) {
        StringBuffer glassIds = new StringBuffer();
        cshtList.forEach(glassId -> glassIds.append("'" + glassId.getGlassId() + "',"));
        glassIds.deleteCharAt(glassIds.length()-1);
        return glassIds.toString();
    }
    private String getGlassIdsFromCPanel(List<CellArrayCfMappingTModel> cPanelList) {
        StringBuffer glassIds = new StringBuffer();
        cPanelList.forEach(glassId -> glassIds.append("'" + glassId.getArrayGlassId() + "',").append("'" + glassId.getCfGlassId() + "',"));
        glassIds.deleteCharAt(glassIds.length()-1);
        return glassIds.toString();
    }

}
